﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data.SqlClient;

namespace EWDT_CDS_Hub.App_Code
{
    public class Announcement
    {
        public int Ann_id { get; set; }
        public String Ann_title { get; set; }
        public String Ann_content { get; set; }
        public DateTime Ann_date { get; set; }
        public int Ann_priority { get; set; }

        public static List<Announcement> GetAnnouncement()
        {
            List<Announcement> results = new List<Announcement>();
            SqlConnection conn = new SqlConnection();

            try
            {                
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EWDTDBString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Announcement";
                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    Announcement a = new Announcement();
                    a.Ann_id = (int)dr["ann_id"];
                    a.Ann_title = (String)dr["ann_title"];
                    a.Ann_content = (String)dr["ann_content"];
                    a.Ann_date = (DateTime)dr["ann_date"];
                    a.Ann_priority = (int)dr["ann_priority"];

                    results.Add(a);
                }                
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }

            return results;
        }

        public static int InsertAnnouncement(Announcement a)
        {
            int rowsInserted = 0;
            SqlConnection conn = new SqlConnection();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EWDTDBString"].ConnectionString;
                conn.Open();

                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "INSERT INTO Announcement (ann_id, ann_title, ann_content, ann_date, ann_priority) VALUES (@id, @title, @content, @date, @priority)";
                comm.Parameters.AddWithValue("@id", a.Ann_id);
                comm.Parameters.AddWithValue("@title", a.Ann_title);
                comm.Parameters.AddWithValue("@content", a.Ann_content);
                comm.Parameters.AddWithValue("@date", a.Ann_date);
                comm.Parameters.AddWithValue("@priority", a.Ann_priority);
                rowsInserted = comm.ExecuteNonQuery();

            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }

            return rowsInserted;
        }

        public static int UpdateAnnouncement(Announcement a)
        {
            int rowsUpdated = 0;
            SqlConnection conn = new SqlConnection();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EWDTDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "UPDATE Announcement SET ann_title = @title, ann_content = @content, ann_date = @date, ann_priority = @priority WHERE ann_id = @id";
                comm.Parameters.AddWithValue("@id", a.Ann_id);
                comm.Parameters.AddWithValue("@title", a.Ann_title);
                comm.Parameters.AddWithValue("@content", a.Ann_content);
                comm.Parameters.AddWithValue("@date", a.Ann_date);
                comm.Parameters.AddWithValue("@priority", a.Ann_priority);
                rowsUpdated = comm.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return rowsUpdated;
        }

        public static Announcement RetrieveAnnouncement(int id)
        {
            Announcement a = null;
            SqlConnection conn = new SqlConnection();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EWDTDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "SELECT * FROM Announcement WHERE ann_id = @id";
                comm.Parameters.AddWithValue("@id", id);
                SqlDataReader dr = comm.ExecuteReader();

                while (dr.Read())
                {
                    a= new Announcement();
                    a.Ann_id = (int)dr["ann_id"];
                    a.Ann_title = (String)dr["ann_title"];
                    a.Ann_content = (String)dr["ann_content"];
                    a.Ann_date = (DateTime)dr["ann_date"];
                    a.Ann_priority = (int)dr["ann_priority"];
                }

                dr.Close();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return a;
        }

        public static int DeleteAnnouncement(int id)
        {
            int rowsDeleted = 0;
            SqlConnection conn = new SqlConnection();

            try
            {
                conn.ConnectionString = ConfigurationManager.ConnectionStrings["EWDTDBString"].ConnectionString;
                conn.Open();
                SqlCommand comm = new SqlCommand();
                comm.Connection = conn;
                comm.CommandText = "DELETE FROM Announcement WHERE ann_id = @id";
                comm.Parameters.AddWithValue("@id", id);
                rowsDeleted = comm.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                conn.Close();
            }
            return rowsDeleted;
        }
    }
}